package de.unistuttgart.iaas.interaction.mail;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

import de.unistuttgart.iaas.interaction.mail.database.MySQLAccess;
import de.unistuttgart.iaas.interaction.mail.util.CommonTools;
import de.unistuttgart.iaas.interaction.mail.util.ConfigFileHandler;
import de.unistuttgart.iaas.interaction.mail.util.LoggingFileHandler;
import de.unistuttgart.iaas.interaction.mail.util.TimeoutComputer;

/**
 * This class handles all mails with mail_class=REQUEST and mail_state=NEW.
 * @author Lina Sun
 *
 */
public class SendMail
{
	
	private static Logger logger = Logger.getLogger(SendMail.class);
	
	public static void sendMail(String configurationData)
	{
		
		ConfigFileHandler config = new ConfigFileHandler(configurationData);
		LoggingFileHandler logFileHandler = new LoggingFileHandler(configurationData);
		Properties properties = new Properties();
		properties.setProperty("log4j.rootCategory", logFileHandler.getMailLogLevel()+", R");
		properties.setProperty("log4j.appender.R", logFileHandler.getMailAppender());
		properties.setProperty("log4j.appender.R.File", logFileHandler.getSendMailLogFilePath());
		properties.setProperty("log4j.appender.R.MaxFileSize", logFileHandler.getMailLogFileSize() );
		properties.setProperty("log4j.appender.R.MaxBackupIndex", logFileHandler.getMailMaxBackupIndex());
		properties.setProperty("log4j.appender.R.layout", logFileHandler.getMailLayout());
		properties.setProperty("log4j.appender.R.layout.ConversionPattern", logFileHandler.getMailPattern());
		PropertyConfigurator.configure( properties );

		logger.info("--------------------SendMailService-----------------------");
		
		MySQLAccess dao = new MySQLAccess();

		String mail_id="";
		String mail_to = "";
		String mail_subject = "";
		String mail_message = "";
		String mail_response = "";
		String mail_responseTemplate = "";
		String mail_timeout= "";
		String filename = "";
		Blob file = null;
		
        int i =0;
        int ii=0;
		try
		{
			Connection conn = dao.connDataBase(config);
			Statement statement = conn.createStatement();
			Session session = AdminConnector.SendConnector(config);			
			MimeMessage msg = new MimeMessage(session);		
			
			ResultSet rs = statement.executeQuery("select mail_id from maildb."+config.getTable_sentmails()+" where mail_state='NEW'");
			ArrayList list_new   = new   ArrayList(); 
			ArrayList list_attId = new ArrayList();

			while(rs.next())
			{ 
				list_new.add(rs.getString(1)); 
				i++;
			}
			statement.close();
			
			for(int j=0;j<i;j++)
			{
				String lv = (String) list_new.get(j);
//				System.out.println(lv);
				Statement sendStatement1 = conn.createStatement();
				ResultSet sendResultset = sendStatement1.executeQuery("select * from maildb."+config.getTable_sentmails()+" where mail_id='"+lv+"'");

				ArrayList sentmail = new ArrayList();
				
				if(sendResultset.next())
				{
					for(int k=1; k<=11;k++)
					{
						
						sentmail.add(sendResultset.getString(k));
					}
				}
				
				mail_id = (String)sentmail.get(0);
				mail_to = (String)sentmail.get(1);
				mail_subject = (String)sentmail.get(3)+" Mail-ID:"+mail_id;
				mail_message = (String)sentmail.get(4)+"\n";
				mail_response = (String)sentmail.get(6);
				mail_responseTemplate = (String)sentmail.get(7);
				if(!mail_responseTemplate.equals(""))
				{
					mail_responseTemplate = CommonTools.xsdToXml(config,mail_responseTemplate);
				}

				
				Statement usernameStatement = conn.createStatement();
				ResultSet usernameResultset = usernameStatement.executeQuery("select username from maildb." + config.getTable_mailregister() + " where mailaddress='" + mail_to +"'");
				
				if(usernameResultset.next())
				{
					mail_message = "Hello " + usernameResultset.getString("username") + ",\n" + mail_message;
				}
				if(mail_response.equalsIgnoreCase("yes"))
				{
					mail_message = mail_message+ "\n\n Required result format:\n"+mail_responseTemplate
					+"\n You can modify this format and copy it in your reply.";
				}
				

				msg.setFrom(new InternetAddress(config.getMailAddress()));		
				msg.setRecipients(Message.RecipientType.TO, InternetAddress.parse(mail_to, false));
				msg.setSubject(mail_subject);
				
				Statement sendStatement2 = conn.createStatement();
				ResultSet rsAttId = sendStatement2.executeQuery("select id from maildb."+config.getTable_mailattachments()+" where mail_id='"+mail_id+"'");
				while(rsAttId.next())
				{
						list_attId.add(rsAttId.getString(1)); 
						ii++;
				}
				sendStatement2.close();
				
				if(list_attId.size()!= 0)
				{	
				    //create the message part
				    MimeBodyPart messageBodyPart = new MimeBodyPart();
				    messageBodyPart.setText(mail_message);
				   				    
				    Multipart multipart = new MimeMultipart();
				    multipart.addBodyPart(messageBodyPart);
				    
				    for(int k=0;k<ii;k++)
				    {
				    	String attId = (String) list_attId.get(k);
//				    	System.out.println(attId);
				    	Statement sendStatement3 = conn.createStatement();
				    	ResultSet rsAtt = sendStatement3.executeQuery("select * from maildb." + config.getTable_mailattachments()+" where id='" + attId +"'");
				    	while(rsAtt.next())
				    	{
							filename=rsAtt.getString("filename");
							file = rsAtt.getBlob("content");
							CommonTools.saveFile(config.getAttPath()+filename, file);
							messageBodyPart = new MimeBodyPart();
							DataSource fileDataSource = new FileDataSource(config.getAttPath()+filename);
							messageBodyPart.setDataHandler(new DataHandler(fileDataSource));
							messageBodyPart.setFileName(filename);
							multipart.addBodyPart(messageBodyPart);
							msg.setContent(multipart); 
				    	}
				    }

				}else
				{
					 // set body
				    msg.setText(mail_message);

				}
				
				Transport transport = session.getTransport("smtp"); 
				
				System.out.println(config.getSendHost()+config.getUsername()+config.getPassword());
			    transport.connect(config.getSendHost(),config.getUsername(),config.getPassword()); 
			    // send e-mail 
			    transport.send(msg);
			    transport.close(); 
			    Calendar cal = Calendar.getInstance();
				DateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  			
				String sentTime = df.format(cal.getTime());
			    Statement update= conn.createStatement();
				
				update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_sentDate='"+sentTime+"', mail_state='SENT' where mail_id='"+mail_id+"'");



			    
			    logger.info("An e-mail has been sent.\n To: "+ mail_to+"\n Subject: "+ mail_subject+"\n");
			    System.out.println("An e-mail has been sent.\n To: "+ mail_to+"\n Subject: "+ mail_subject+"\n");
			}

		}catch(Exception e)
		{
			logger.error("Error in SendMail.");
			e.printStackTrace();
			System.out.println("Error in SendMail.");
		}
		
		
	}
}
